package cn.com.doone.common.uc.infrastructure.jdbc;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Repository;

import com.sun.jmx.snmp.Timestamp;

import cn.com.doone.common.uc.domain.oauth.AccessToken;
import cn.com.doone.common.uc.domain.oauth.ClientDetails;
import cn.com.doone.common.uc.domain.oauth.OauthCode;
import cn.com.doone.common.uc.domain.oauth.OauthRepository;
import cn.com.doone.common.uc.infrastructure.DateUtils;
import cn.com.doone.common.uc.infrastructure.jdbc.AbstractJdbcRepository;
import cn.com.doone.common.uc.infrastructure.jdbc.AccessTokenRowMapper;
import cn.com.doone.common.uc.infrastructure.jdbc.ClientDetailsRowMapper;
import cn.com.doone.common.uc.infrastructure.jdbc.OauthCodeRowMapper;
import cn.com.doone.common.uc.service.dto.IpBlackList;
import cn.com.doone.common.uc.service.dto.LoginLog;
import cn.com.doone.common.uc.utils.DES3;

/**
 * 15-6-13
 *
 * @author Shengzhao Li
 */
@Repository("oauthJdbcRepository")
public class OauthJdbcRepository extends AbstractJdbcRepository implements OauthRepository {


    private static ClientDetailsRowMapper clientDetailsRowMapper = new ClientDetailsRowMapper();
    private static OauthCodeRowMapper oauthCodeRowMapper = new OauthCodeRowMapper();

    private AccessTokenRowMapper accessTokenRowMapper = new AccessTokenRowMapper();
    
    @Override
    public ClientDetails findClientDetails(String clientId) {
        final String sql = " select APP_CODE AS client_id,APP_SECRET AS client_secret,APP_NAME AS client_name,APP_URI AS client_uri,APP_ICON_URI AS client_icon_uri," +
				" RESOURCE_IDS AS resource_ids,SCOPE AS scope,GRANT_TYPES AS grant_types,REDIRECT_URI AS redirect_uri,ROLES AS roles,ACCESS_TOKEN_VALIDITY AS access_token_validity," +
				" REFRESH_TOKEN_VALIDITY AS refresh_token_validity,DESCRIPTION AS description,CREATE_TIME AS create_time,ARCHIVED AS archived,TRUSTED AS trusted" +
				" from app_info where ARCHIVED = 0 and APP_CODE = ? ";
        final List<ClientDetails> list = jdbcTemplate.query(sql, clientDetailsRowMapper, clientId);
        return list.isEmpty() ? null : list.get(0);
        
    }

    @Override
    public int saveClientDetails(final ClientDetails clientDetails) {
        final String sql = " insert into oauth_client_details(client_id,client_secret,client_name, client_uri,client_icon_uri,resource_ids, scope,grant_types, " +
                "redirect_uri,roles,access_token_validity,refresh_token_validity,description,archived,trusted) values (?,?,?, ?,?,?,?,?, ?,?, ?,? ,?,?,?)";

        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, clientDetails.getClientId());
                ps.setString(2, clientDetails.getClientSecret());
                ps.setString(3, clientDetails.getName());

                ps.setString(4, clientDetails.getClientUri());
                ps.setString(5, clientDetails.getIconUri());
                ps.setString(6, clientDetails.resourceIds());

                ps.setString(7, clientDetails.scope());
                ps.setString(8, clientDetails.grantTypes());
                ps.setString(9, clientDetails.getRedirectUri());

                ps.setString(10, clientDetails.roles());
                ps.setInt(11, clientDetails.accessTokenValidity() == null ? -1 : clientDetails.accessTokenValidity());
                ps.setInt(12, clientDetails.refreshTokenValidity() == null ? -1 : clientDetails.refreshTokenValidity());

                ps.setString(13, clientDetails.getDescription());
                ps.setBoolean(14, clientDetails.archived());
                ps.setBoolean(15, clientDetails.trusted());
            }
        });
    }

    @Override
    public int saveOauthCode(final OauthCode oauthCode) {
        final String sql = " insert into oauth_code(code,username,client_id) values (?,?,?)";
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, oauthCode.code());
                ps.setString(2, oauthCode.username());
                ps.setString(3, oauthCode.clientId());
            }
        });
    }

    @Override
    public OauthCode findOauthCode(String code, String clientId) {
        final String sql = " select * from oauth_code where code = ? and client_id = ? ";
        final List<OauthCode> list = jdbcTemplate.query(sql, oauthCodeRowMapper, code, clientId);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public OauthCode findOauthCodeByUsernameClientId(String username, String clientId) {
        final String sql = " select * from oauth_code where username = ? and client_id = ? ";
        final List<OauthCode> list = jdbcTemplate.query(sql, oauthCodeRowMapper, username, clientId);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public int deleteOauthCode(final OauthCode oauthCode) {
        final String sql = " delete from oauth_code where code = ? and client_id = ? and username = ?";
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, oauthCode.code());
                ps.setString(2, oauthCode.clientId());
                ps.setString(3, oauthCode.username());
            }
        });
    }

    @Override
    public AccessToken findAccessToken(String clientId, String username, String authenticationId) {
        final String sql = " select * from oauth_access_token where client_id = ? and username = ? and authentication_id = ?";
        final List<AccessToken> list = jdbcTemplate.query(sql, accessTokenRowMapper, clientId, username, authenticationId);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public int deleteAccessToken(final AccessToken accessToken) {
        final String sql = " delete from oauth_access_token where client_id = ? and username = ? and authentication_id = ?";
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, accessToken.clientId());
                ps.setString(2, accessToken.username());
                ps.setString(3, accessToken.authenticationId());
            }
        });
    }

    @Override
    public int saveAccessToken(final AccessToken accessToken) {
        final String sql = "insert into oauth_access_token(token_id,token_expired_seconds,authentication_id," +
                "username,client_id,token_type,refresh_token_expired_seconds,refresh_token) values (?,?,?,?,?,?,?,?) ";

        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, accessToken.tokenId());
                ps.setInt(2, accessToken.tokenExpiredSeconds());
                ps.setString(3, accessToken.authenticationId());

                ps.setString(4, accessToken.username());
                ps.setString(5, accessToken.clientId());
                ps.setString(6, accessToken.tokenType());

                ps.setInt(7, accessToken.refreshTokenExpiredSeconds());
                ps.setString(8, accessToken.refreshToken());
            }
        });
    }

    @Override
    public AccessToken findAccessTokenByRefreshToken(String refreshToken, String clientId) {
        final String sql = " select * from oauth_access_token where refresh_token = ? and client_id = ? ";
        final List<AccessToken> list = jdbcTemplate.query(sql, accessTokenRowMapper, refreshToken, clientId);
        return list.isEmpty() ? null : list.get(0);
    }
    
    @Override
    public Map<String, Object> findAppInfo(String appCode) {
    	final String sql = "select ai.* from app_info ai where ai.APP_CODE = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, appCode);
    	return list.isEmpty() ? null : list.get(0);
    }
    
    @Override
    public Map<String, Object> findUserInfoByUserAccount(String userAccount) {
    	final String sql = "select ui.* from user_info ui where ui.USER_ACCOUNT = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userAccount); 
    	return list.isEmpty() ? null : list.get(0);
    }
    
    @Override
    public Map<String, Object> findUserGroupAppAuthorize(int userGroupId, int appInfoId) {
    	final String sql = "select ugaa.* from user_group_app_authorize ugaa where ugaa.USER_GROUP_ID = ? and ugaa.APP_INFO_ID = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userGroupId, appInfoId); 
    	return list.isEmpty() ? null : list.get(0);
    }
    
    @Override
    public boolean valiUserGroupAndAppAuthorize(int userId, int appInfoId) {
    	final String sql = "select count(*) from USER_GROUP_APP_AUTHORIZE ugaa where ugaa.USER_GROUP_ID in (select ugm.USER_GROUP_ID from USER_GROUP_MEMBER ugm where ugm.USER_INFO_ID = ?) " +
    					"and ugaa.APP_INFO_ID = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userId, appInfoId}, Integer.class);
     	return count == 0 ? false : true;
    }
    
    @Override
    public boolean valiUserAndAppAuthorize(int userId, int appInfoId) {
    	final String sql = "select count(*) from USER_APP_AUTHORIZE uaa where uaa.USER_INFO_ID = ? and uaa.APP_INFO_ID = ? AND uaa.EXPIRED_TIME > NOW()";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userId, appInfoId}, Integer.class);
    	return count == 0 ? false : true;
    }
    
    @Override
    public boolean valiIPBlackList(String ip) {
    	final String sql = "select count(*) from ip_black_list ibl where ibl.IP = ? and ibl.INVALID_TIME >= ? AND LOCK_LEVEL = '5'";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {ip, DateUtils.toDateText(new Date(), DateUtils.DEFAULT_DATE_TIME_FORMAT)}, Integer.class);
    	return count == 0 ? false : true;
    }
    
    @Override
    public void saveIPBlackListInfo(final IpBlackList ipBlackList) {
    	// IP数据是否已存在黑名单中
    	if(this.valiIPExist(ipBlackList.getIP())) {	// 已存在执行更新
    		final String sql = "update ip_black_list ibl set ibl.UPDATE_TIME = now(), ibl.INVALID_TIME = ?, ibl.REMARKS = ?, ibl.LOCK_LEVEL = ? where ibl.IP = ?";
    		
        	jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                	ps.setString(1, DateUtils.toDateTime(ipBlackList.getInvalidTime()));
                    ps.setString(2, ipBlackList.getRemarks());
                    ps.setString(3, ipBlackList.getLockLevel());
                    ps.setString(4, ipBlackList.getIP());
                }
            });
    		
    	} else {							// 不存在执行新增
    		final String sql = "insert into ip_black_list(IP,CREATE_TIME,INVALID_TIME,REMARKS,UPDATE_TIME,LOCK_LEVEL) values (?, NOW(), ?, ?,NOW(),?)";
    		jdbcTemplate.update(sql, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, ipBlackList.getIP());
                    ps.setString(2, DateUtils.toDateTime(ipBlackList.getInvalidTime()));
                    ps.setString(3, ipBlackList.getRemarks());
                    ps.setString(4, ipBlackList.getLockLevel());
                }
            });
    	}
    }
    
    @Override
    public void deleteIPBlackListInfo(String ip) {
    	final String sql = "delete from ip_black_list where ip = ?";
    	jdbcTemplate.update(sql, ip);
    }
    
    @Override
    public void updateIPBlackListInfoByIP(String ip, String invalidTime, String remarks) {
    	final String sql = "update ip_black_list ibl set ibl.INVALID_TIME = ?, ibl.REMARKS = ? where ibl.IP = ?";
    	jdbcTemplate.update(sql, invalidTime, remarks, ip);
    }
    
    @Override
    public void updateIPBlackListInfoByID(int id, String ip, String invalidTime, String remarks) {
    	final String sql = "update ip_black_list ibl set ibl.IP = ?,ibl.INVALID_TIME = ?, ibl.REMARKS = ? where ibl.IP_BLACK_LIST_ID = ?";
    	jdbcTemplate.update(sql, ip, invalidTime, remarks, id);
    }
    
    @Override
    public boolean valiIPExist(String ip) {
    	final String sql = "select count(*) from ip_black_list ibl where ibl.IP = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {ip}, Integer.class);
    	return count == 0 ? false : true;
    }
    
    @Override
    public boolean valiIPExitBlackList(String ip) {
    	final String sql = "select count(*) from ip_black_list ibl where ibl.IP = ? AND LOCK_LEVEL = '5'";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {ip}, Integer.class);
    	return count == 0 ? false : true;
    }
    
    @Override
    public boolean valiIPWhiteList(String ip) {
    	final String sql = "select count(*) from ip_white_list iwl where iwl.IP = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {ip}, Integer.class);
    	return count == 0 ? false : true;
    }
    
    @Override
    public List<Map<String, Object>> findIPBlackList(String searchWhere) {
    	final String sql = "select ibl.* from ip_black_list ibl " + searchWhere;
    	return jdbcTemplate.queryForList(sql);
    }
    
    @Override
    public List<Map<String, Object>> findIPWhiteList(String searchWhere) {
    	final String sql = "select iwl.* from ip_white_list iwl " + searchWhere;
    	return jdbcTemplate.queryForList(sql);
    }
    
    public List<Map<String, Object>> findUserInfo() {
    	final String sql = "SELECT USER_INFO_ID AS userInfoId, USER_ACCOUNT AS userAccount, REALNAME AS realname FROM user_info WHERE STATUS != '0'";
    	return jdbcTemplate.queryForList(sql);
    }
    
    public void addUserInfo(final String userAccount, final String realname) {
    	final String sql = "insert INTO user_info_copy (user_account, realname) VALUE (?,?)";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, userAccount);
                ps.setString(2, realname);
            }
        });
    }
    	
	@Override
    public void deleteUserAppAuthorize(int userId, int appInfoId) {
    	final String sql = "DELETE FROM user_app_authorize WHERE user_info_id = ? AND app_info_id = ?";
    	jdbcTemplate.update(sql, userId, appInfoId);
    }
    
	@Override
    public void saveGroupAppAuthorize(int userGroupId, int appInfoId) {
    	final String sql = "insert into user_group_app_authorize(USER_GROUP_ID, APP_INFO_ID, AUTH_TIME, EXPIRED_TIME, DEFAULT_AUTH) values(?,  ?, now(), now(), '1')";
    	jdbcTemplate.update(sql, userGroupId, appInfoId);
    }
	
    @Override
    public void saveUserAppAuthorize(int userId, int appInfoId) {
    	final String sql = "insert into user_app_authorize(USER_INFO_ID, DOONE_CODE, APP_INFO_ID, AUTH_TIME, EXPIRED_TIME) values(?, (SELECT DOONE_CODE FROM USER_INFO WHERE USER_INFO_ID = ?), ?, now(), date_add(NOW(), interval 1 month))";
    	jdbcTemplate.update(sql, userId, userId, appInfoId);
    }
    
    public boolean validWeakPassword(String password) {
    	Calendar cal = Calendar.getInstance();
		int year = cal.get(Calendar.YEAR);
		
		try {
			password = DES3.decode(password, "ZGJMWQJSTMDZQQRDOONE" + String.valueOf(year));
		} catch (Exception e) {
			e.printStackTrace();
		}
    	final String sql = "select count(*) from weak_password where password = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {password}, Integer.class);
    	return count == 0 ? false : true;
    }

	@Override
	public boolean isCorrectPassword(String userAccount, String password) {
		final String sql = "select count(*) from user_info where USER_ACCOUNT = ? AND PASSWORD = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userAccount, password}, Integer.class);
    	return count == 0 ? false : true;
	}

	@Override
	public void changePassword(String userAccount, String password,String p_code) {
		final String sql = "update user_info set PASSWORD = ?, P_CODE = ?,ENCRYPTION_TYPE = '2',UPDATE_TIME=? where USER_ACCOUNT = ?";
    	jdbcTemplate.update(sql, password, p_code,new Date(),userAccount);
	}

	@Override
	public void saveLoginLog(final LoginLog loginLog) {
		// TODO Auto-generated method stub
		final String sql = "INSERT INTO LOGIN_LOG (USER_INFO_ID, DOONE_CODE, USER_ACCOUNT, LOGIN_TIME, IS_SUCCESS, IP_ADDRESS, DEVICE, MAC_ADDRESS) VALUE (?,?,?,NOW(),?,?,?,?)";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, loginLog.getUserInfoId());
                ps.setInt(2, loginLog.getDooneCode());
                ps.setString(3, loginLog.getUserAccount());
                ps.setString(4, loginLog.getIsSuccess());
                ps.setString(5, loginLog.getIpAddress());
                ps.setString(6, loginLog.getDevice());
                ps.setString(7, loginLog.getMacAddress());
            }
        });
	}

	@Override
	public int findUserInfoIdByAccount(String userAccount) {
		final String sql = "SELECT IFNULL((select USER_INFO_ID from user_info where USER_ACCOUNT = ?), 0)";
    	final int userInfoId = jdbcTemplate.queryForObject(sql, new Object[] {userAccount}, Integer.class);
		return userInfoId;
	}

	@Override
	public int countLoginLogByIp(String ip, String startTime, String endTime) {
		final String sql = "SELECT" +
								" COUNT(LOGIN_LOG_ID)" +
							" FROM" +
								" login_log" +
							" WHERE" +
								" IP_ADDRESS = ?" +
							" AND IS_SUCCESS = '0'" +
							" AND LOGIN_TIME >= ?" +
							" AND LOGIN_TIME <= ?" +
							" AND LOGIN_TIME >= (SELECT" +
							" LOGIN_TIME" +
							" FROM" +
							" LOGIN_LOG" +
							" WHERE" +
							" IP_ADDRESS = ?" +
							" AND IS_SUCCESS = '1'" +
							" ORDER BY LOGIN_TIME DESC LIMIT 1)";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {ip, startTime, endTime, ip}, Integer.class);
    	return count;
	}

	@Override
	public int countLoginLogByUserId(int userInfoId, String startTime, String endTime) {
		
		final String sql = "SELECT" +
				" COUNT(LOGIN_LOG_ID)" +
			" FROM" +
				" login_log" +
			" WHERE" +
				" USER_INFO_ID = ?" +
			" AND IS_SUCCESS = '0'" +
			" AND LOGIN_TIME >= ?" +
			" AND LOGIN_TIME <= ?" +
			" AND LOGIN_TIME >= (SELECT" +
			" LOGIN_TIME" +
			" FROM" +
			" LOGIN_LOG" +
			" WHERE" +
			" USER_INFO_ID = ?" +
			" AND IS_SUCCESS = '1'" +
			" ORDER BY LOGIN_TIME DESC LIMIT 1)";
		final int count = jdbcTemplate.queryForObject(sql, new Object[] {userInfoId, startTime, endTime, userInfoId}, Integer.class);
		return count;
	}

	@Override
	public void updateUserLock(Map<String, Object> userMap) {
		final String sql = "UPDATE USER_INFO SET LOCK_LEVEL = ?, INVALID_TIME = ? WHERE USER_INFO_ID = ?";
    	jdbcTemplate.update(sql, userMap.get("lockLevel"), userMap.get("invalidTime"), userMap.get("userInfoId"));
	}

	@Override
	public Map<String, Object> findUserInfoByUserAccount(String appCode, String userAccount) {
		final String sql = "SELECT " +
								" userInfo.USER_INFO_ID," +
								" userInfo.DOONE_CODE," +
								" userInfo.USER_ACCOUNT," +
								" userInfo.REALNAME," +
								" appAccountBinding.AUTH_ACCOUNT" +
							" FROM " +
								" user_info userInfo," +
								" app_account_binding appAccountBinding" +
							" WHERE " +
								" userInfo.USER_INFO_ID = appAccountBinding.USER_INFO_ID" +
							" AND appAccountBinding.APP_CODE = ?" +
							" AND appAccountBinding.USER_ACCOUNT = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, appCode, userAccount); 
    	return list.isEmpty() ? null : list.get(0);
	}
	
	/**
	 * 根据账号查看用户信息
	 * 
	 * @param userAccount
	 * @return
	 */
	public Map<String, Object> findUserInfoByUserAccountSimple(String userAccount) {
		final String sql = "SELECT " +
								" userInfo.USER_INFO_ID," +
								" userInfo.DOONE_CODE," +
								" userInfo.USER_ACCOUNT," +
								" userInfo.REALNAME" +
							" FROM " +
								" user_info userInfo" +
							" WHERE " +
								" userInfo.USER_ACCOUNT = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userAccount); 
    	return list.isEmpty() ? null : list.get(0);
	}
	
	@Override
	public Map<String, Object> findUserInfoByAppAccount(String appCode, String appAccount) {
		final String sql = "SELECT " +
								" userInfo.USER_INFO_ID," +
								" userInfo.DOONE_CODE," +
								" userInfo.USER_ACCOUNT," +
								" userInfo.REALNAME" +
							" FROM " +
								" user_info userInfo," +
								" app_account_binding appAccountBinding" +
							" WHERE " +
								" userInfo.USER_INFO_ID = appAccountBinding.USER_INFO_ID" +
							" AND appAccountBinding.APP_CODE = ?" +
							" AND appAccountBinding.AUTH_ACCOUNT = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, appCode, appAccount); 
    	return list.isEmpty() ? null : list.get(0);
	}

	@Override
	public Map<String, Object> findWedoStaffIdByAccount(String appCode,String userAccount) {
		
		final String sql = "SELECT" +
								" group_concat(AUTH_ACCOUNT SEPARATOR ',') AS wedoUserIds" +
							" FROM" +
								" app_account_binding" +
							" WHERE" +
								" USER_ACCOUNT IN (" + userAccount  + ")" +
							" AND APP_CODE = ?";
		
		/*System.out.println(sql);
		
		return jdbcTemplate.queryForMap(sql, userAccount, appCode);*/
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, appCode); 
    	return list.isEmpty() ? null : list.get(0);
	}

	@Override
	public void saveAccountBinding(final Map<String, Object> userMap) {
		
		final String sql = "INSERT INTO APP_ACCOUNT_BINDING (USER_INFO_ID, DOONE_CODE, USER_ACCOUNT, AUTH_TYPE, AUTH_ACCOUNT, APP_CODE, REALNAME) VALUE (?,?,?,3,?,?,?)";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, (Integer) userMap.get("USER_INFO_ID"));
                ps.setInt(2, (Integer) userMap.get("DOONE_CODE"));
                ps.setString(3, (String) userMap.get("USER_ACCOUNT"));
                ps.setString(4, (String) userMap.get("AUTH_ACCOUNT"));
                ps.setString(5, (String) userMap.get("APP_CODE"));
                ps.setString(6, (String) userMap.get("REALNAME"));
            }
        });
		
	}

	@Override
	public Map<String, Object> findAppInfoByWeChatId(String clientId, String appid) {
		final String sql = "SELECT * FROM app_info WHERE APP_CODE = ? AND WECHAT_APP_ID = ?";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, clientId, appid); 
		return list.isEmpty() ? null : list.get(0);
	}
	
	@Override
	public List<Map<String, Object>> findWechatIdList(String clientId, String appCode) {
		final String sql = " SELECT"
							+ " appAccountBinding.*"
						+ " FROM"
							+ " user_info userInfo,"
							+ " user_group userGroup,"
							+ " user_group_member userGroupMember,"
							+ " user_group_app_authorize userGroupApp,"
							+ " app_info appInfo,"
							+ " app_account_binding appAccountBinding"
						+ " WHERE"
							+ " userInfo.USER_INFO_ID = userGroupMember.USER_INFO_ID"
						+ " AND"
							+ " userGroup.USER_GROUP_ID = userGroupMember.USER_GROUP_ID"
						+ " AND"
							+ " userGroupApp.USER_GROUP_ID = userGroup.USER_GROUP_ID"
						+ " AND"
							+ " appInfo.APP_INFO_ID = userGroupApp.APP_INFO_ID"
						+ " AND"
							+ " appAccountBinding.USER_INFO_ID = userInfo.USER_INFO_ID"
						+ " AND"
							+ " appInfo.APP_CODE = ?"
						+ " AND"
							+ " appAccountBinding.APP_CODE = ?";
		
		return jdbcTemplate.queryForList(sql, clientId, appCode); 
	}
	
	@Override
	public Map<String, Object> findWechatIdByUserInfoId(int userInfoId, String appCode) {
		final String sql = "SELECT * FROM app_account_binding WHERE USER_INFO_ID = ? AND APP_CODE = ?";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userInfoId, appCode); 
		return list.isEmpty() ? null : list.get(0);
	}

	@Override
	public Map<String, Object> findUserInfoByUserId(String userId) {
		final String sql = "select ui.* from user_info ui where ui.USER_INFO_ID = ?";
    	final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userId); 
    	return list.isEmpty() ? null : list.get(0);
	}

	@Override
	public void changePasswordByUserInfoId(String userInfoId, String password, String p_code) {
		final String sql = "update user_info set PASSWORD = ?, P_CODE = ?,ENCRYPTION_TYPE = '2',UPDATE_TIME=? where USER_INFO_ID = ?";
    	int n = jdbcTemplate.update(sql, password, p_code,new Date(),userInfoId);
    	System.out.println(n);
	}

	@Override
	public boolean isCorrectPasswordByUserInfoId(String userInfoId, String password) {
		final String sql = "select count(*) from user_info where USER_INFO_ID = ? AND PASSWORD = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userInfoId, password}, Integer.class);
    	return count == 0 ? false : true;
	}

	@Override
	public List<Integer> findUserGroupByUserId(int userInfoId) {
		final String sql = "SELECT USER_GROUP_ID FROM user_group_member WHERE USER_INFO_ID = ? AND `STATUS` = '1'";
    	final List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class, userInfoId); 
    	return list;
	}

	@Override
	public int countUserGroupAndAppAuthorize(int userGroupId, int appInfoId) {
		final String sql = "SELECT" +
								" count(userGroupAppAuthorize.USER_GROUP_ID)" +
							" FROM" +
								" user_group_app_authorize userGroupAppAuthorize," +
								" app_info appInfo" +
							" WHERE" +
								" FIND_IN_SET(userGroupAppAuthorize.USER_GROUP_ID,getParentGroupList (?))" +
							" AND" +
							 	" userGroupAppAuthorize.APP_INFO_ID = appInfo.APP_INFO_ID" +
							" AND" +
								" appInfo.`STATUS` = '1'" +
							" AND" + 
								" appInfo.APP_INFO_ID = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userGroupId, appInfoId}, Integer.class); 
    	return count;
	}

	@Override
	public Map<String, Object> queryInfoByToken(String token) {
		final String sql = "select username from oauth_access_token where token_id=?";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, token); 
		return list.isEmpty() ? null : list.get(0);
	}
    
}
